#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into checkin_dws.dws_student_attend
with t as (
select
class_id,
class_date,
student_id,
substr(class_date,1,4) as yearinfo,
substr(class_date,6,2) as monthinfo,
substr(class_date,9,2) as dayinfo,


case when sum(if(signin_time >= cast(date_add('minute',-40, date_parse(concat(class_date,' ',morning_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR )
and signin_time <= cast(date_add('minute',10, date_parse(concat(class_date,' ',morning_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR ),1,0))>0 then 'ontime'
when sum(if(signin_time > cast(date_add('minute',10, date_parse(concat(class_date,' ',morning_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR )
and signin_time <= concat(class_date,' ',morning_end_time),1,0))>0 then 'late'
else null end as morning_attend,


case when sum(if(signin_time >= cast(date_add('minute',-40, date_parse(concat(class_date,' ',afternoon_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR )
and signin_time <= cast(date_add('minute',10, date_parse(concat(class_date,' ',afternoon_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR ),1,0))>0 then 'ontime'
when sum(if(signin_time > cast(date_add('minute',10, date_parse(concat(class_date,' ',afternoon_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR )
and signin_time <= concat(class_date,' ',afternoon_end_time),1,0))>0 then 'late'
else null end as afternoon_attend,

case when sum(if(signin_time >= cast(date_add('minute',-40, date_parse(concat(class_date,' ',evening_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR )
and signin_time <= cast(date_add('minute',10, date_parse(concat(class_date,' ',evening_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR ),1,0))>0 then 'ontime'
when sum(if(signin_time > cast(date_add('minute',10, date_parse(concat(class_date,' ',evening_begin_time), '%Y-%m-%d %H:%i:%s')) as VARCHAR )
and signin_time <= concat(class_date,' ',evening_end_time),1,0))>0 then 'late'
else null end as evening_attend

from checkin_dwb.dwb_student_attend
group by substr(class_date,1,4),substr(class_date,6,2),substr(class_date,9,2),class_id,class_date,student_id
having class_id is not null)
select
    class_id,
    class_date,
    yearinfo,
    monthinfo,
    dayinfo,
    count(case when morning_attend in('ontime', 'late')then student_id else null end) as morning_attend_count,
    count(case when afternoon_attend in('ontime', 'late') then student_id else null end) as afternoon_attend_count,
    count(case when evening_attend in('ontime', 'late') then student_id else null end) as evening_attend_count,
    count(case when morning_attend ='ontime' then student_id else null end) as morning_ontime_count,
    count(case when afternoon_attend ='ontime' then student_id else null end) as afternoon_ontime_count,
    count(case when evening_attend = 'ontime' then student_id else null end) as evening_ontime_count,
    count(case when morning_attend ='late' then student_id else null end) as morning_late_count,
    count(case when afternoon_attend ='late' then student_id else null end) as afternoon_late_count,
    count(case when evening_attend ='late' then student_id else null end ) as evening_late_count

from t
group by  yearinfo,monthinfo,dayinfo,class_id,class_date
having class_id is not null;



insert into checkin_dws.dws_student_leave
select
class_id,
class_date,
substr(class_date,1,4) as yearinfo,
substr(class_date,6,2) as monthinfo,
substr(class_date,9,2) as dayinfo,
count(distinct if(begin_time <= concat(class_date,' ',morning_begin_time) and end_time >= concat(class_date,' ',morning_end_time) ,student_id,null)) as morning_leave_count,
count(distinct if(begin_time <= concat(class_date,' ',afternoon_begin_time) and end_time >= concat(class_date,' ',afternoon_end_time) ,student_id,null)) as afternoon_leave_count,
count(distinct if(begin_time <= concat(class_date,' ',evening_begin_time) and end_time >= concat(class_date,' ',evening_end_time) ,student_id,null)) as evening_leave_count
from checkin_dwb.dwb_student_leave
group by substr(class_date,1,4),substr(class_date,6,2),substr(class_date,9,2),class_id,class_date
having class_id is not null;



insert into checkin_dws.dws_student_absent
select
ctud.class_id,
ctud.class_date,
substr(ctud.class_date,1,4) as yearinfo,
substr(ctud.class_date,6,2) as monthinfo,
substr(ctud.class_date,9,2) as dayinfo,
COALESCE(cssc.studying_student_count,0) - COALESCE(sa.morning_attend_count, 0) - COALESCE  (sl.morning_leave_count,0),
COALESCE(cssc.studying_student_count,0) - COALESCE(sa.afternoon_attend_count, 0) - COALESCE(sl.afternoon_leave_count,0),
COALESCE(cssc.studying_student_count,0) - COALESCE(sa.evening_attend_count, 0) - COALESCE  (sl.evening_leave_count,0)

from (select * from checkin_dwd.dim_dwd_course_table_upload_detail where content is not null and content != '开学典礼') ctud
left join checkin_dws.dws_student_attend sa on ctud.class_id = sa.class_id and ctud.class_date = sa.class_date
left join checkin_dws.dws_student_leave sl on ctud.class_id = sl.class_id and ctud.class_date = sl.class_date
left join checkin_dwd.dim_dwd_class_studying_student_count cssc on cssc.class_id = ctud.class_id and cssc.studying_date = ctud.class_date
where studying_student_count is not null;
"